package vip.xiaonuo.smzq.modular.hisi.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.hisi.entity.AsyncHead;
import vip.xiaonuo.smzq.modular.hisi.entity.HisiKs;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.*;
import vip.xiaonuo.smzq.modular.jsc.entity.vo.Dykcfb;

import java.util.List;
import java.util.Map;

/**
 * 矿山信息 Mapper
 *
 * @author eomer
 * @since 2024-02-22
 */
@Mapper
public interface HisiKsMapper extends BaseMapper<HisiKs> {
    @Select("WITH bigkqby AS ( SELECT SUM ( kqbyl ) AS bigby FROM hisi_ks WHERE djflbh = '1000' AND gm = '大型' ${sql} ),\n" +
            "bigcount AS ( SELECT COUNT ( * ) AS bigkcd FROM hisi_ks WHERE djflbh = '1000' AND gm = '大型' ${sql} ),\n" +
            "mediumkqby AS ( SELECT SUM ( kqbyl ) AS mediumkqby FROM hisi_ks WHERE djflbh = '1000' AND gm = '中型' ${sql} ),\n" +
            "mediumcount AS ( SELECT COUNT ( * ) AS mediumkcd FROM hisi_ks WHERE djflbh = '1000' AND gm = '中型' ${sql}),\n" +
            "smallkqby AS ( SELECT SUM ( kqbyl ) AS smallkqby FROM hisi_ks WHERE djflbh = '1000' AND gm = '小型' ${sql} ),\n" +
            "smallcount AS ( SELECT COUNT ( * ) AS smallkcd FROM hisi_ks WHERE djflbh = '1000' AND gm = '小型' ${sql} ) \n" +
            "SELECT * FROM bigkqby,bigcount,mediumkqby,mediumcount,smallkqby,smallcount")
    Kcdgmtj selectKcdgmtj(@Param(value = "sql") String sql);

    @Select("${sql}")
    List<ZyclcxVo> selectKqbh(@Param(value = "sql") String sql);


    @Select("WITH zyltj AS ( SELECT SUM ( kqbyl ) AS zyltj FROM hisi_ks WHERE djflbh = '1000' ${sql}) ,\n" +
            "     kcsl AS ( SELECT COUNT ( * ) AS kcsl FROM hisi_ks WHERE djflbh = '1000' ${sql})\n" +
            "select * from zyltj,kcsl")
    Dykcfb getDykcfb(@Param(value = "sql") String sql);

    @Select("select nd from hisi_kshz where nd is not null GROUP BY nd")
    List<Integer> getNdList();


    //矿山数据同步存储过程
    @Select("select * from kqbhlist(#{nd},#{xzqhbm})  ORDER BY kqbh,djflbh")
    List<HisiKs> getupdateKsData(@Param("nd") Integer nd, @Param("xzqhbm") String xzqhbm);

    @Select("WITH kcl_zycl_filtered AS (\n" +
            "  SELECT z.kcdm, z.tjdx, SUM(z.ksl) AS total_sum\n" +
            "  FROM kcl_zycl z\n" +
            "  WHERE z.zycllb = 19001 AND  z.nd=#{nd} and z.kqbh=#{kqbh} and z.djflbh=#{djflbh} and z.kcdm=#{kcdm} and z.tjdx=#{tjdx}\n" +
            "  GROUP BY z.kcdm, z.tjdx\n" +
            ")\n" +
            "SELECT \n" +
            "  CASE \n" +
            "    WHEN (kcl_zycl_filtered.total_sum / kcl_gmdz.dwhs ) BETWEEN 0 AND zxxx THEN '小型'\n" +
            "    WHEN (kcl_zycl_filtered.total_sum / kcl_gmdz.dwhs ) BETWEEN zxxx AND dxxx THEN '中型'\n" +
            "    ELSE '大型'\n" +
            "  END AS size_category\n" +
            "FROM \n" +
            "  kcl_zycl_filtered\n" +
            "JOIN \n" +
            "  kcl_cb15_kczyclgmdz AS kcl_gmdz\n" +
            "ON \n" +
            "  kcl_zycl_filtered.kcdm = kcl_gmdz.kcdm\n" +
            "  AND kcl_zycl_filtered.tjdx = kcl_gmdz.tjdx;")
    String getGm(@Param("nd") Integer nd, @Param("kqbh") String kqbh, @Param("kcdm") Integer kcdm, @Param("tjdx") Integer tjdx, @Param("djflbh") Integer djflbh);

    @Select("with \n" +
            "totalby as(\n" +
            "select sum(kqbyl) as byl from hisi_ks where djflbh='1000' and xzqdm like CONCAT('%'|| #{xzqhdm} || '%') and kcdm=#{kcdm} and nd=#{nd}\n" +
            "),\n" +
            "\n" +
            " lyby as(\n" +
            "select sum(kqbyl) as byl from hisi_ks where djflbh='1000' and xzqdm LIKE CONCAT('%'|| #{xzqhdm} || '%') and kcdm=#{kcdm} and sfly=1 and nd=#{nd}\n" +
            ")\n" +
            "\n" +
            "select (lyby.byl / totalby.byl) as lyl from totalby,lyby")
    double getlyl(@Param("nd") Integer nd, @Param("xzqhdm") String xzqhdm, @Param("kcdm") String kcdm);


    @Select("WITH \n" +
            "  zj AS (SELECT ROUND(COUNT(*), 2) AS zj FROM hisi_ks ${sql}),\n" +
            "  byl AS (SELECT ROUND(SUM(ksbyl), 2) AS byl FROM hisi_ks ${sql}),\n" +
            "  ljl AS (SELECT ROUND(SUM(ksljl), 2) AS ljl FROM hisi_ks ${sql}),\n" +
            "  kcl AS (SELECT ROUND(SUM(kskcl), 2) AS kcl FROM hisi_ks ${sql}),\n" +
            "  ssl AS (SELECT ROUND(SUM(ksssl), 2) AS ssl FROM hisi_ks ${sql}),\n" +
            "  kczjl AS (SELECT ROUND(SUM(kskczjl), 2) AS kczjl FROM hisi_ks ${sql}),\n" +
            "  cszjl AS (SELECT ROUND(SUM(kscszjl), 2) AS cszjl FROM hisi_ks ${sql}),\n" +
            "  qtl AS (SELECT ROUND(SUM(ksqtl), 2) AS qtl FROM hisi_ks ${sql}),\n" +
            "  spyfl AS (SELECT ROUND(SUM(ksspyfl), 2) AS spyfl FROM hisi_ks ${sql})\n" +
            "SELECT * FROM zj, byl, ljl, kcl, ssl, kczjl, cszjl, qtl, spyfl;")
    Cxtjsljs getCxtslis(@Param(value = "sql") String sql);

    @Select("select sum(kqzsl) as kqzsl,\n" +
            "sum(kqkxl) as kqkxl,\n" +
            "sum(kqtml) as kqtml,\n" +
            "sum(kqkzl) as kqkzl,\n" +
            "sum(kqtdl) as kqtdl,\n" +
            "(sum(kqzsl) + sum(kqkxl)) as zskxhjl,\n" +
            "(sum(kqtml) + sum(kqkzl) + sum(kqtdl)) as tmkztdl\n" +
            "from hisi_ks\n" +
            "${ew.customSqlSegment}")
    List<ZyclCount> selectZyclCount(@Param(Constants.WRAPPER) Wrapper<Map> queryWrapper);

    /**
     * 资源储量总数量查询
     *
     * @param sql
     * @return
     */
    @Select("select sum(kqzsl) as zsl,\n" +
            "sum(kqkxl) as kxl,\n" +
            "sum(kqtml) as tml,\n" +
            "sum(kqkzl) as kzl,\n" +
            "sum(kqtdl) as tdl,\n" +
            "(sum(kqzsl) + sum(kqkxl)) as zskxhjl,\n" +
            "(sum(kqtml) + sum(kqkzl) + sum(kqtdl)) as tmkztdl\n" +
            "from hisi_ks\n" +
            "where 1=1 ${sql}")
    List<ZyclCount> selectZyclCount2(@Param(value = "sql") String sql);

    /**
     * 资源储量总条数查询
     *
     * @param sql
     * @return
     */
    @Select("select count(1) from hisi_ks\n" +
            "where 1=1 ${sql}\n" +
            "GROUP BY kqbh, kcdm, nd")
    List<Map> selectZyclZj(@Param(value = "sql") String sql);


    /**
     * 根据年度查询这几年的变化量
     *
     * @param ndList 年度列表
     * @return List<Map < String, Object>>
     */
    List<Map<String, Object>> getzyclBhl(List<Integer> ndList);

    /**
     * 根据年度查询这几年的变化量的总条数
     *
     * @param ndList   年度列表
     * @param kcdm     矿产代码
     * @param kqbh     矿区编号
     * @param xzqhList 行政区列表
     * @param kqmc     矿区名称
     * @return List<Map < String, Object>>
     */
    List<Map<String, Object>> getzyclBhlCountByPage(List<Integer> ndList,
                                                    @Param("kcdm") Integer kcdm, @Param("kqbh") String kqbh, List<Integer> xzqhList, @Param("kqmc") String kqmc, @Param("tjdx") Integer tjdx);

    /**
     * 根据年度查询这几年的变化量
     *
     * @param ndList   年度列表
     * @param kcdm     矿产代码
     * @param kqbh     矿区编号
     * @param xzqhList 行政区列表
     * @param kqmc     矿区名称
     * @return List<Map < String, Object>>
     */
    List<Map<String, Object>> getzyclBhlByPage(List<Integer> ndList, @Param("current") long current, @Param("size") long size,
                                               @Param("kcdm") Integer kcdm, @Param("kqbh") String kqbh, List<Integer> xzqhList, @Param("kqmc") String kqmc, @Param("tjdx") Integer tjdx);

    /**
     * 根据年度查询这几年的变化量
     *
     * @param ndList 年度列表
     * @return List<Map < String, Object>>
     */
    List<Map<String, Object>> getzyclBhlList(List<Integer> ndList,
                                             @Param("kcdm") Integer kcdm, @Param("kqbh") String kqbh, List<Integer> xzqhList, @Param("kqmc") String kqmc, @Param("tjdx") Integer tjdx);

    /**
     * 矿产地规模统计-重构
     *
     * @param nd
     * @param kcdm
     * @author Kevin
     * @since 2024/7/5 19:11
     */
    List<KcdgmtjVo> getKcdgmtjListNew(@Param("nd") Integer nd, @Param("kcdm") Integer kcdm);

    /**
     * 资源储量查询统计-重构
     *
     * @param page
     * @param queryWrapper
     * @author Kevin
     * @since 2024/7/6 17:57
     */
    @Select("with base as (\n" +
            " with \n" +
            "     kszh as ( select hz.nd, hz.xzqdm,hz.fxzqdm, hz.kcdm, hz.tjdx ,hz.kqbh,hz.jsdxmc,hz.sbbsm  from hisi_kshz hz ${ew.customSqlSegment}   " +
            "               GROUP BY hz.nd, hz.xzqdm,hz.fxzqdm, hz.kcdm, hz.tjdx ,hz.kqbh,hz.jsdxmc,hz.sbbsm )\n" +
            "           select zy.nd, hz.xzqdm, xian.mc as xian, shi.mc as shi, sheng.mc as sheng, zy.kqbh,zy.kcmc, zy.kcdm, zy.jsldw, zy.ksldw, zy.tjdx, hz.jsdxmc,\n" +
            "           CAST(sum(case when zy.zycllx = 'ZS' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as zs_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'ZS' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as zs_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'KX' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as kx_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'KX' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as kx_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'TM' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as tm_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'TM' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as tm_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'KZ' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as kz_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'KZ' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as kz_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'TD' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as td_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'TD' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as td_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'QZ' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as qz_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'QZ' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as qz_nmbyksl\n" +
            "           \n" +
            "           from hisi_ks_zyclhz zy\n" +
            "           left join kszh hz on hz.nd = zy.nd and hz.kqbh = zy.kqbh and hz.kcdm = zy.kcdm and hz.tjdx = zy.tjdx\n" +
            "           left join kcl_cb08_xzqh xian on hz.xzqdm = cast(xian.dm as TEXT)\n" +
            "           left join kcl_cb08_xzqh shi on hz.fxzqdm = cast(shi.dm as TEXT)\n" +
            "           left join kcl_cb08_xzqh sheng on shi.fdm = sheng.dm\n" +
            "           ${ew.customSqlSegment} \n" +
            "          group by zy.nd, hz.xzqdm, xian.mc, shi.mc, sheng.mc, zy.kqbh, zy.kcmc, zy.kcdm, zy.jsldw, zy.ksldw, zy.tjdx, hz.jsdxmc)\n" +
            "\n" +
            "           SELECT\n" +
            "\t base.nd,\n" +
            "\t\tbase.xzqdm,\n" +
            "\t\tbase.xian,\n" +
            "\t\tbase. shi,\n" +
            "\t\tbase. sheng,\n" +
            "\t\tbase.kqbh,\n" +
            "\t\tbase.kcmc,\n" +
            "\t\tbase.kcdm,\n" +
            "\t\tbase.jsldw,\n" +
            "\t\tbase.ksldw,\n" +
            "\t\tbase.tjdx,\n" +
            "\t\tbase.jsdxmc, " +
            "base.zs_nmbyjsl,\n" +
            "          base.zs_nmbyksl,\n" +
            "          base.kx_nmbyjsl,\n" +
            "          base.kx_nmbyksl,\n" +
            "          base.tm_nmbyjsl,\n" +
            "          base.tm_nmbyksl,\n" +
            "          base.kz_nmbyjsl,\n" +
            "          base.kz_nmbyksl,\n" +
            "          base.td_nmbyjsl,\n" +
            "          base.td_nmbyksl,\n" +
            "          base.qz_nmbyjsl,\n" +
            "          base.qz_nmbyksl,\n" +
            "           base.sheng || '>' || base.shi || '>' || base.xian as xzqhmc,\n" +
            "           CAST((base.zs_nmbyjsl + base.kx_nmbyjsl) AS DECIMAL(18,2)) as zs_kx_nmbyjsl,\n" +
            "           CAST((base.zs_nmbyksl + base.kx_nmbyksl) AS DECIMAL(18,2)) as zs_kx_nmbyksl,\n" +
            "           CAST((base.tm_nmbyjsl + base.kz_nmbyjsl + base.td_nmbyjsl) AS DECIMAL(18,2)) as tm_kz_td_nmbyjsl,\n" +
            "           CAST((base.tm_nmbyksl + base.kz_nmbyksl + base.td_nmbyksl) AS DECIMAL(18,2)) as tm_kz_td_nmbyksl FROM\n" +
            "\tbase " +
            "GROUP BY\n" +
            " base.nd,\n" +
            "\t\tbase.xzqdm,\n" +
            "\t\tbase.xian,\n" +
            "\t\tbase. shi,\n" +
            "\t\tbase. sheng,\n" +
            "\t\tbase.kqbh,\n" +
            "\t\tbase.kcmc,\n" +
            "\t\tbase.kcdm,\n" +
            "\t\tbase.jsldw,\n" +
            "\t\tbase.ksldw," +
            "base.zs_nmbyjsl,\n" +
            "          base.zs_nmbyksl,\n" +
            "          base.kx_nmbyjsl,\n" +
            "          base.kx_nmbyksl,\n" +
            "          base.tm_nmbyjsl,\n" +
            "          base.tm_nmbyksl,\n" +
            "          base.kz_nmbyjsl,\n" +
            "          base.kz_nmbyksl,\n" +
            "          base.td_nmbyjsl,\n" +
            "          base.td_nmbyksl,\n" +
            "          base.qz_nmbyjsl,\n" +
            "          base.qz_nmbyksl,\n" +
            "\t\tbase.tjdx,\n" +
            "\t\tbase.jsdxmc,\n" +
            "\t\tzs_kx_nmbyjsl,\n" +
            "\t\tzs_kx_nmbyksl,\n" +
            "\t\ttm_kz_td_nmbyjsl,\n" +
            "\t\txzqhmc,\n" +
            "\t\ttm_kz_td_nmbyksl\n" +
            "           order by base.nd, base.xzqdm, base.kcmc, base.jsldw, base.ksldw")
    Page<ZyclVo> pageZyclVo(IPage<ZyclVo> page, @Param(Constants.WRAPPER) Wrapper<Map> queryWrapper);

    /**
     * 资源储量查询统计总量-重构
     *
     * @param queryWrapper
     * @author Kevin
     * @since 2024/7/6 17:58
     */
//    @Select("with base as (\n" +
//            "select zy.nd, hz.xzqdm, zy.kqbh, hz.kqmc, zy.kcmc, zy.kcdm, zy.jsldw, zy.ksldw,\n" +
//            "sum(case when zy.zycllx = 'ZS' then zy.nmbyjsl else 0 end) as zs_nmbyjsl,\n" +
//            "sum(case when zy.zycllx = 'ZS' then zy.nmbyksl else 0 end) as zs_nmbyksl,\n" +
//            "\n" +
//            "sum(case when zy.zycllx = 'KX' then zy.nmbyjsl else 0 end) as kx_nmbyjsl,\n" +
//            "sum(case when zy.zycllx = 'KX' then zy.nmbyksl else 0 end) as kx_nmbyksl,\n" +
//            "\n" +
//            "sum(case when zy.zycllx = 'TM' then zy.nmbyjsl else 0 end) as tm_nmbyjsl,\n" +
//            "sum(case when zy.zycllx = 'TM' then zy.nmbyksl else 0 end) as tm_nmbyksl,\n" +
//            "\n" +
//            "sum(case when zy.zycllx = 'KZ' then zy.nmbyjsl else 0 end) as kz_nmbyjsl,\n" +
//            "sum(case when zy.zycllx = 'KZ' then zy.nmbyksl else 0 end) as kz_nmbyksl,\n" +
//            "\n" +
//            "sum(case when zy.zycllx = 'TD' then zy.nmbyjsl else 0 end) as td_nmbyjsl,\n" +
//            "sum(case when zy.zycllx = 'TD' then zy.nmbyksl else 0 end) as td_nmbyksl,\n" +
//            "\n" +
//            "sum(case when zy.zycllx = 'QZ' then zy.nmbyjsl else 0 end) as qz_nmbyjsl,\n" +
//            "sum(case when zy.zycllx = 'QZ' then zy.nmbyksl else 0 end) as qz_nmbyksl\n" +
//            "\n" +
//            "from hisi_ks_zyclhz zy\n" +
//            "left join hisi_kshz hz on hz.nd = zy.nd and hz.kqbh = zy.kqbh and hz.kcdm = zy.kcdm\n" +
//            "${ew.customSqlSegment}\n" +
//            "group by zy.nd, hz.xzqdm, zy.kqbh, hz.kqmc, zy.kcmc, zy.kcdm, zy.jsldw, zy.ksldw)\n" +
//            "\n" +
//            "select (select count(1) from base) as zj,\n" +
//            "CAST(sum(base.zs_nmbyjsl) AS DECIMAL(18,2)) as zs_nmbyjsl,\n" +
//            "CAST(sum(base.zs_nmbyksl) AS DECIMAL(18,2)) as zs_nmbyksl,\n" +
//            "\n" +
//            "CAST(sum(base.kx_nmbyjsl) AS DECIMAL(18,2)) as kx_nmbyjsl,\n" +
//            "CAST(sum(base.kx_nmbyksl) AS DECIMAL(18,2)) as kx_nmbyksl,\n" +
//            "\n" +
//            "CAST(sum(base.tm_nmbyjsl) AS DECIMAL(18,2)) as tm_nmbyjsl,\n" +
//            "CAST(sum(base.tm_nmbyksl) AS DECIMAL(18,2)) as tm_nmbyksl,\n" +
//            "\n" +
//            "CAST(sum(base.kz_nmbyjsl) AS DECIMAL(18,2)) as kz_nmbyjsl,\n" +
//            "CAST(sum(base.kz_nmbyksl) AS DECIMAL(18,2)) as kz_nmbyksl,\n" +
//            "\n" +
//            "CAST(sum(base.td_nmbyjsl) AS DECIMAL(18,2)) as td_nmbyjsl,\n" +
//            "CAST(sum(base.td_nmbyksl) AS DECIMAL(18,2)) as td_nmbyksl,\n" +
//            "\n" +
//            "CAST((sum(base.zs_nmbyjsl) + sum(base.kx_nmbyjsl)) AS DECIMAL(18,2)) as zs_kx_nmbyjsl,\n" +
//            "CAST((sum(base.zs_nmbyksl) + sum(base.kx_nmbyksl)) AS DECIMAL(18,2)) as zs_kx_nmbyksl,\n" +
//            "CAST((sum(base.tm_nmbyjsl) + sum(base.kz_nmbyjsl) + sum(base.td_nmbyjsl)) AS DECIMAL(18,2)) as tm_kz_td_nmbyjsl,\n" +
//            "CAST((sum(base.tm_nmbyksl) + sum(base.kz_nmbyksl) + sum(base.td_nmbyksl)) AS DECIMAL(18,2)) as tm_kz_td_nmbyksl\n" +
//            "from base")
//    List<ZyclCountVo> getZyclTotal(@Param(Constants.WRAPPER) Wrapper<Map> queryWrapper);
    @Select("with base as (\n" +
            " with \n" +
            "     kszh as ( select hz.nd, hz.xzqdm,hz.fxzqdm, hz.kcdm, hz.tjdx ,hz.kqbh,hz.jsdxmc,hz.sbbsm   from hisi_kshz hz ${ew.customSqlSegment}  " +
            "            GROUP BY hz.nd, hz.xzqdm,hz.fxzqdm, hz.kcdm, hz.tjdx ,hz.kqbh,hz.jsdxmc ,hz.sbbsm)\n" +
            "           select zy.nd, hz.xzqdm, xian.mc as xian, shi.mc as shi, sheng.mc as sheng, zy.kqbh,zy.kcmc, zy.kcdm, zy.jsldw, zy.ksldw, zy.tjdx, hz.jsdxmc,\n" +
            "           CAST(sum(case when zy.zycllx = 'ZS' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as zs_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'ZS' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as zs_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'KX' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as kx_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'KX' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as kx_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'TM' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as tm_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'TM' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as tm_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'KZ' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as kz_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'KZ' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as kz_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'TD' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as td_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'TD' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as td_nmbyksl,\n" +
            "           \n" +
            "           CAST(sum(case when zy.zycllx = 'QZ' then zy.nmbyjsl else 0 end) AS DECIMAL(18,2)) as qz_nmbyjsl,\n" +
            "           CAST(sum(case when zy.zycllx = 'QZ' then zy.nmbyksl else 0 end) AS DECIMAL(18,2)) as qz_nmbyksl\n" +
            "           \n" +
            "           from hisi_ks_zyclhz zy\n" +
            "           left join kszh hz on hz.nd = zy.nd and hz.kqbh = zy.kqbh and hz.kcdm = zy.kcdm and hz.tjdx = zy.tjdx\n" +
            "           left join kcl_cb08_xzqh xian on hz.xzqdm = cast(xian.dm as TEXT)\n" +
            "           left join kcl_cb08_xzqh shi on hz.fxzqdm = cast(shi.dm as TEXT)\n" +
            "           left join kcl_cb08_xzqh sheng on shi.fdm = sheng.dm\n" +
            "           ${ew.customSqlSegment} \n" +
            "          group by zy.nd, hz.xzqdm, xian.mc, shi.mc, sheng.mc, zy.kqbh, zy.kcmc, zy.kcdm, zy.jsldw, zy.ksldw, zy.tjdx, hz.jsdxmc)\n" +
            "select (select count(1) from base) as zj,\n" +
            "CAST(sum(base.zs_nmbyjsl) AS DECIMAL(18,2)) as zs_nmbyjsl,\n" +
            "CAST(sum(base.zs_nmbyksl) AS DECIMAL(18,2)) as zs_nmbyksl,\n" +
            "\n" +
            "CAST(sum(base.kx_nmbyjsl) AS DECIMAL(18,2)) as kx_nmbyjsl,\n" +
            "CAST(sum(base.kx_nmbyksl) AS DECIMAL(18,2)) as kx_nmbyksl,\n" +
            "\n" +
            "CAST(sum(base.tm_nmbyjsl) AS DECIMAL(18,2)) as tm_nmbyjsl,\n" +
            "CAST(sum(base.tm_nmbyksl) AS DECIMAL(18,2)) as tm_nmbyksl,\n" +
            "\n" +
            "CAST(sum(base.kz_nmbyjsl) AS DECIMAL(18,2)) as kz_nmbyjsl,\n" +
            "CAST(sum(base.kz_nmbyksl) AS DECIMAL(18,2)) as kz_nmbyksl,\n" +
            "\n" +
            "CAST(sum(base.td_nmbyjsl) AS DECIMAL(18,2)) as td_nmbyjsl,\n" +
            "CAST(sum(base.td_nmbyksl) AS DECIMAL(18,2)) as td_nmbyksl,\n" +
            "\n" +
            "CAST((sum(base.zs_nmbyjsl) + sum(base.kx_nmbyjsl)) AS DECIMAL(18,2)) as zs_kx_nmbyjsl,\n" +
            "CAST((sum(base.zs_nmbyksl) + sum(base.kx_nmbyksl)) AS DECIMAL(18,2)) as zs_kx_nmbyksl,\n" +
            "CAST((sum(base.tm_nmbyjsl) + sum(base.kz_nmbyjsl) + sum(base.td_nmbyjsl)) AS DECIMAL(18,2)) as tm_kz_td_nmbyjsl,\n" +
            "CAST((sum(base.tm_nmbyksl) + sum(base.kz_nmbyksl) + sum(base.td_nmbyksl)) AS DECIMAL(18,2)) as tm_kz_td_nmbyksl\n" +
            "from base")
    List<ZyclCountVo> getZyclTotal(@Param(Constants.WRAPPER) Wrapper<Map> queryWrapper);

    /**
     * 根据矿种查询各个规模下的矿区编号
     *
     * @param sql
     * @return
     */
    // @Select("select kqbh,kcdm,gm,xzqdm from hisi_kshz where ${sql}  group by kqbh,kcdm,gm ,xzqdm")
    @Select("select kqbh,kcdm,gm from hisi_kshz where sbbsm in ('1','2') and ${sql}  group by kqbh,kcdm,gm ")
    List<Map> getKqbhList(@Param(value = "sql") String sql);


//     @Select("WITH nmbyjsl AS (\n" +
//             "\tSELECT SUM\n" +
//             "\t\t( zy.nmbyjsl ) AS nmbyjsl \n" +
//             "\tFROM hisi_ks_zyclhz zy WHERE  ${sql} AND zy.zycllx in ('TM','KZ','TD')\n" +
//             "\t),\n" +
//             "\tnmbyksl AS (\n" +
//             "\tSELECT SUM\n" +
//             "\t\t( zy.nmbyksl ) AS nmbyksl \n" +
//             "\tFROM hisi_ks_zyclhz zy WHERE  ${sql} AND zy.zycllx in ('TM','KZ','TD')\n" +
//             "\t) SELECT\n" +
//             "\tnmbyjsl.*,\n" +
//             "\tnmbyksl.* \n" +
//             "FROM\n" +
//             "\tnmbyjsl,\n" +
//             "\tnmbyksl\n" +
//             "\t\t")

    /**
     * 查询各省市的资源量
     *
     * @param sql
     * @return
     */

    @Select("WITH \n" +
            "    nmbyjsl AS ( SELECT SUM ( zy.nmbyjsl ) AS nmbyjsl FROM hisi_ks_zyclhz zy WHERE ${sql} AND  zy.zycllx IN ( 'TM', 'KZ', 'TD' ) ),\n" +
            "    nmbyksl AS ( SELECT SUM ( zy.nmbyksl ) AS nmbyksl FROM hisi_ks_zyclhz zy WHERE ${sql} AND  zy.zycllx IN ( 'TM', 'KZ', 'TD' ) ) \n" +
            "SELECT \n" +
            "\t  COALESCE(nmbyjsl.nmbyjsl, 0) AS nmbyjsl, COALESCE(nmbyksl.nmbyksl, 0) AS nmbyksl\n" +
            " FROM nmbyjsl,nmbyksl ")
    Map selectzyl(@Param(value = "sql") String sql);

    /**
     * 驾驶舱矿产列表
     *
     * @return
     */
    @Select("SELECT hz.kcdm AS code,hz.kcmc AS title,CASE  WHEN kc.jsldw IS NOT NULL AND kc.jsldw <> '' THEN kc.jsldw ELSE kc.ksldw END AS dw\n" +
            "FROM hisi_kshz hz LEFT JOIN kcl_cb06_kc kc ON (hz.kcdm = kc.kcdm AND hz.tjdx = kc.tjdx) where xzqdm like #{xzqhbm} || '%' GROUP BY hz.kcdm, hz.kcmc, dw\n" +
            "ORDER BY hz.kcdm")
    List<AsyncHead> getJscKcdmList(String xzqhdm);


    /**
     * 矿产地规模统计2024.07.29
     * @param sql
     * @return
     */
    @Select("WITH TJ AS (\n" +
            "    SELECT k.kqbh, zy.kcdm, zy.tjdx,\n" +
            "           COALESCE(SUM(CASE WHEN (zy.zycllb = 19002 AND c.gmbs = 0) THEN zy.jsl \n" +
            "                             WHEN (zy.zycllb = 19002 AND c.gmbs = 1) THEN zy.ksl \n" +
            "                             ELSE 0 END), 0) AS total_sum,\n" +
            "           COALESCE(SUM(CASE WHEN (zy.zycllb = 19001  ) THEN zy.jsl ELSE 0 END), 0) AS by_jsl,\n" +
            "           COALESCE(SUM(CASE WHEN (zy.zycllb = 19001  ) THEN zy.ksl ELSE 0 END), 0) AS by_ksl\n" +
            "    FROM kcl_ks k \n" +
            "    LEFT JOIN kcl_zycl zy ON (zy.nd = k.nd AND zy.kqbh = k.kqbh AND zy.djflbh = k.djflbh)\n" +
            "    LEFT JOIN kcl_cb06_kc c ON (zy.kcdm = c.kcdm AND c.tjdx = zy.tjdx) \n" +
            "    WHERE  k.sbbs <> 3 AND zy.zycllx IN ('TM', 'KZ', 'TD') \n" +
            "      AND ${sql}\n" +
            "    GROUP BY k.kqbh, zy.kcdm, zy.tjdx\n" +
            ")\n" +
            "SELECT \n" +
            "T.kcdm, MC.kcmc, T.tjdx, MC.jsdxmc, gmtype, COUNT(1) AS gmcount, \n" +
            "    COALESCE(to_number(to_char(SUM(total_sum), '9999999999.99'), '9999999999.99'), 0) AS ljl,\n" +
            "    COALESCE(to_number(to_char(SUM(by_jsl), '9999999999.99'), '9999999999.99'), 0) AS byjsl,\n" +
            "    COALESCE(to_number(to_char(SUM(by_ksl), '9999999999.99'), '9999999999.99'), 0) AS byksl," +
            "    COALESCE (to_number( to_char( SUM (case when MC.xsbs = 0 then by_jsl else by_ksl end), '9999999999.99' ), '9999999999.99' ), 0 ) byl\n" +
            "FROM (\n" +
            "    SELECT TJ.*, \n" +
            "           CASE \n" +
            "               WHEN (TJ.total_sum / BZ.dwhs) BETWEEN 0 AND BZ.zxxx THEN '小型'\n" +
            "               WHEN (TJ.total_sum / BZ.dwhs) BETWEEN BZ.zxxx AND BZ.dxxx THEN '中型'\n" +
            "               ELSE '大型'\n" +
            "           END AS gmtype\n" +
            "    FROM TJ\n" +
            "    LEFT JOIN kcl_cb15_kczyclgmdz BZ ON TJ.kcdm = BZ.kcdm AND TJ.tjdx = BZ.tjdx\n" +
            ") T\n" +
            "LEFT JOIN kcl_cb06_kc MC ON T.kcdm = MC.kcdm AND T.tjdx = MC.tjdx\n" +
            "GROUP BY T.kcdm, MC.kcmc, T.tjdx, MC.jsdxmc, gmtype;")
    List<Map> getGmtj(@Param(value = "sql") String sql);



    @Select("select (kcdm || '_' || tjdx) as kc from kcl_zycl where nd=#{nd} GROUP BY kc ORDER BY kc")
    List<String> getKcdm(Integer nd);


    /**
     * 资源储量重构2024.07.30
     * sqq
     * @param iPage
     * @param queryWrapper
     * @param nd
     * @return
     */
    @Select("with tj as (\n" +
            "select zy.nd, zy.kcdm, zy.tjdx, kc.kcmc, kc.jsdxmc, ks.kqbh, kc.jsldw, kc.ksldw,\n" +
            "      sum(case when zy.zycllx = 'ZS' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) zsNmbyjsl,\n" +
            "      sum(case when zy.zycllx = 'KX' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) kxNmbyjsl,\n" +
            "      sum(case when zy.zycllx in ('ZS','KX') and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) zsKxNmbyjsl,\n" +
            "      sum(case when zy.zycllx = 'TM' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) tmNmbyjsl,\n" +
            "      sum(case when zy.zycllx = 'KZ' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) kzNmbyjsl,\n" +
            "      sum(case when zy.zycllx = 'TD' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) tdNmbyjsl,\n" +
            "      sum(case when zy.zycllx in('TM','KZ','TD') and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) tmKzTdNmbyjsl,\n" +
            "      \n" +
            "      sum(case when zy.zycllx = 'ZS' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) zsNmbyksl,\n" +
            "      sum(case when zy.zycllx = 'KX' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) kxNmbyksl,\n" +
            "      sum(case when zy.zycllx in ('ZS','KX') and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) zsKxNmbyksl,\n" +
            "      sum(case when zy.zycllx = 'TM' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) tmNmbyksl,\n" +
            "      sum(case when zy.zycllx = 'KZ' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) kzNmbyksl,\n" +
            "      sum(case when zy.zycllx = 'TD' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) tdNmbyksl,\n" +
            "      sum(case when zy.zycllx in('TM','KZ','TD') and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) tmKzTdNmbyksl\n" +
            "from kcl_ks ks\n" +
            "left join kcl_zycl zy on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh\n" +
            "left join kcl_cb06_kc kc on kc.kcdm = zy.kcdm and kc.tjdx = zy.tjdx \n" +
            " ${ew.customSqlSegment} \n" +
            "group by zy.nd, zy.kcdm, zy.tjdx, kc.kcmc, kc.jsdxmc, kc.jsldw, kc.ksldw,  ks.kqbh \n" +
            ")\n" +
            "select tmp.nd, (sheng.mc || '>' || shi.mc || '>' || xian.mc) AS xzqhmc, \n" +
            "       tmp.ksmc as kqmc, \n" +
            "       tmp.kqbh, tmp.kcdm, tmp.tjdx, tmp.kcmc, tmp.jsdxmc, tmp.jsldw, tmp.ksldw, \n" +
            "       tmp.zsNmbyksl, tmp.kxNmbyksl, tmp.zsKxNmbyksl, tmp.tmNmbyksl, tmp.kzNmbyksl, tmp.tdNmbyksl, tmp.tmKzTdNmbyksl,\n" +
            "       tmp.zsNmbyjsl, tmp.kxNmbyjsl, tmp.zsKxNmbyjsl, tmp.tmNmbyjsl, tmp.kzNmbyjsl, tmp.tdNmbyjsl, tmp.tmKzTdNmbyjsl\n" +
            "       from \n" +
            "(select tj.*, ks.djflbh, ks.ksmc, ks.xzqdm, \n" +
            "        rank() over(partition by ks.kqbh order by djflbh) rk\n" +
            "        from tj \n" +
            "        join kcl_ks ks on tj.nd = ks.nd and tj.kqbh = ks.kqbh) tmp\n" +
            "        \n" +
            "left JOIN kcl_cb08_xzqh xian ON tmp.xzqdm = xian.dm\n" +
            "LEFT JOIN kcl_cb08_xzqh shi ON shi.dm = xian.fdm\n" +
            "LEFT JOIN kcl_cb08_xzqh sheng ON sheng.dm = shi.fdm\n" +
            "where tmp.rk = 1\n" +
            "order by tmp.xzqdm, tmp.kqbh, tmp.kcdm, tmp.kcdm, tmp.tjdx ")
//    Page<ZyclVo> pageZyclVoNew(IPage<ZyclVo> iPage, @Param(Constants.WRAPPER) Wrapper<Map> queryWrapper, @Param("nd") Integer nd);

    Page<ZyclVo> pageZyclVoNew( @Param("iPage") Page<ZyclVo> iPage,@Param("kcdm") Integer kcdm, @Param("tjdx") Integer tjdx, @Param("xzqh") String xzqh, @Param("nd") Integer nd,
                               @Param("kqmc") String kqmc, @Param("kqbh") String kqbh, @Param("xzqhList") List<Integer> xzqhList);




    /**
     * 资源储量总计重构2024.07.30
     * sqq
     * @param queryWrapper
     * @param nd
     * @return
     */
//    @Select("with enddata  as (\n" +
//            "with tj as (\n" +
//            "select zy.nd, zy.kcdm, zy.tjdx, kc.kcmc, kc.jsdxmc, ks.kqbh, kc.jsldw, kc.ksldw,\n" +
//            "      sum(case when zy.zycllx = 'ZS' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) zsNmbyjsl,\n" +
//            "      sum(case when zy.zycllx = 'KX' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) kxNmbyjsl,\n" +
//            "      sum(case when zy.zycllx in ('ZS','KX') and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) zsKxNmbyjsl,\n" +
//            "      sum(case when zy.zycllx = 'TM' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) tmNmbyjsl,\n" +
//            "      sum(case when zy.zycllx = 'KZ' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) kzNmbyjsl,\n" +
//            "      sum(case when zy.zycllx = 'TD' and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) tdNmbyjsl,\n" +
//            "      sum(case when zy.zycllx in('TM','KZ','TD') and zy.zycllb = 19001 then jsl else 0 end)::numeric(10,2) tmKzTdNmbyjsl,\n" +
//            "      \n" +
//            "      sum(case when zy.zycllx = 'ZS' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) zsNmbyksl,\n" +
//            "      sum(case when zy.zycllx = 'KX' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) kxNmbyksl,\n" +
//            "      sum(case when zy.zycllx in ('ZS','KX') and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) zsKxNmbyksl,\n" +
//            "      sum(case when zy.zycllx = 'TM' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) tmNmbyksl,\n" +
//            "      sum(case when zy.zycllx = 'KZ' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) kzNmbyksl,\n" +
//            "      sum(case when zy.zycllx = 'TD' and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) tdNmbyksl,\n" +
//            "      sum(case when zy.zycllx in('TM','KZ','TD') and zy.zycllb = 19001 then ksl else 0 end)::numeric(10,2) tmKzTdNmbyksl\n" +
//            "from kcl_ks ks\n" +
//            "left join kcl_zycl zy on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh\n" +
//            "left join kcl_cb06_kc kc on kc.kcdm = zy.kcdm and kc.tjdx = zy.tjdx \n" +
//            "${ew.customSqlSegment} \n" +
//            "group by zy.nd, zy.kcdm, zy.tjdx, kc.kcmc, kc.jsdxmc, kc.jsldw, kc.ksldw,  ks.kqbh \n" +
//            ")\n" +
//            "select tmp.nd, (sheng.mc || '>' || shi.mc || '>' || xian.mc) AS xzqhmc, \n" +
//            "       tmp.ksmc as kqmc, \n" +
//            "       tmp.kqbh, tmp.kcdm, tmp.tjdx, tmp.kcmc, tmp.jsdxmc, tmp.jsldw, tmp.ksldw, \n" +
//            "       tmp.zsNmbyksl, tmp.kxNmbyksl, tmp.zsKxNmbyksl, tmp.tmNmbyksl, tmp.kzNmbyksl, tmp.tdNmbyksl, tmp.tmKzTdNmbyksl,\n" +
//            "       tmp.zsNmbyjsl, tmp.kxNmbyjsl, tmp.zsKxNmbyjsl, tmp.tmNmbyjsl, tmp.kzNmbyjsl, tmp.tdNmbyjsl, tmp.tmKzTdNmbyjsl\n" +
//            "       from \n" +
//            "(select tj.*, ks.djflbh, ks.ksmc, ks.xzqdm, \n" +
//            "        rank() over(partition by ks.kqbh order by djflbh) rk\n" +
//            "        from tj \n" +
//            "        join kcl_ks ks on tj.nd = ks.nd and tj.kqbh = ks.kqbh) tmp\n" +
//            "        \n" +
//            "left JOIN kcl_cb08_xzqh xian ON tmp.xzqdm = xian.dm\n" +
//            "LEFT JOIN kcl_cb08_xzqh shi ON shi.dm = xian.fdm\n" +
//            "LEFT JOIN kcl_cb08_xzqh sheng ON sheng.dm = shi.fdm\n" +
//            "where tmp.rk = 1\n" +
//            "order by tmp.xzqdm, tmp.kqbh, tmp.kcdm, tmp.kcdm, tmp.tjdx\n" +
//            ")\n" +
//            "\n" +
//            "SELECT \n" +
//            "        (select count(1) from enddata) as zj, \n" +
//            "        CAST(sum(ed.zsnmbyjsl) AS DECIMAL(18,2)) as zs_nmbyjsl,\n" +
//            "        CAST(sum(ed.zsnmbyksl) AS DECIMAL(18,2)) as zs_nmbyksl,\n" +
//            "\n" +
//            "        CAST(sum(ed.kxnmbyjsl) AS DECIMAL(18,2)) as kx_nmbyjsl,\n" +
//            "        CAST(sum(ed.kxnmbyksl) AS DECIMAL(18,2)) as kx_nmbyksl,\n" +
//            "      \n" +
//            "        CAST(sum(ed.tmnmbyjsl) AS DECIMAL(18,2)) as tm_nmbyjsl,\n" +
//            "        CAST(sum(ed.tmnmbyksl) AS DECIMAL(18,2)) as tm_nmbyksl,\n" +
//            "\n" +
//            "        CAST(sum(ed.kznmbyjsl) AS DECIMAL(18,2)) as kz_nmbyjsl,\n" +
//            "        CAST(sum(ed.kznmbyksl) AS DECIMAL(18,2)) as kz_nmbyksl,\n" +
//            "   \n" +
//            "        CAST(sum(ed.tdnmbyjsl) AS DECIMAL(18,2)) as td_nmbyjsl,\n" +
//            "        CAST(sum(ed.tdnmbyksl) AS DECIMAL(18,2)) as td_nmbyksl,\n" +
//            "       \n" +
//            "        CAST((sum(ed.zsnmbyjsl) + sum(ed.kxnmbyjsl)) AS DECIMAL(18,2)) as zs_kx_nmbyjsl,\n" +
//            "        CAST((sum(ed.zsnmbyksl) + sum(ed.kxnmbyksl)) AS DECIMAL(18,2)) as zs_kx_nmbyksl,\n" +
//            "        CAST((sum(ed.tmnmbyjsl) + sum(ed.kznmbyjsl) + sum(ed.tdnmbyjsl)) AS DECIMAL(18,2)) as tm_kz_td_nmbyjsl,\n" +
//            "        CAST((sum(ed.tmnmbyksl) + sum(ed.kznmbyksl) + sum(ed.tdnmbyksl)) AS DECIMAL(18,2)) as tm_kz_td_nmbyksl     \n" +
//            "  FROM enddata ed")
//    List<ZyclCountVo> getZyclTotalNew(@Param(Constants.WRAPPER) Wrapper<Map> queryWrapper,@Param("nd") Integer nd);
    ZyclCountVo getZyclTotalNew(@Param("kcdm") Integer kcdm, @Param("tjdx") Integer tjdx, @Param("xzqh") String xzqh, @Param("nd") Integer nd,
                                      @Param("kqmc") String kqmc, @Param("kqbh") String kqbh, @Param("xzqhList") List<Integer> xzqhList);


    @Select("\tWITH bigcount AS (\n" +
            "\tSELECT COUNT\n" +
            "\t\t( * ) AS bigkcd \n" +
            "\tFROM\n" +
            "\t\t\t( SELECT  ks.kqbh, ks.kcdm, ks.tjdx FROM hisi_kshz ks  left join hisi_ks_zyclhz zy\n" +
            "on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh and ks.kcdm = zy.kcdm and ks.tjdx = zy.tjdx\n" +
            "WHERE ks.gm = '大型'  and ks.sbbsm <>3 and zy.zycllx in ('TM', 'KZ', 'TD') ${sql} GROUP BY  ks.kqbh, ks.kcdm, ks.tjdx ) AS big \n" +
            "\t),\n" +
            "\tmediumcount AS (\n" +
            "\tSELECT COUNT\n" +
            "\t\t( * ) AS mediumkcd \n" +
            "\tFROM\n" +
            "\t\t( SELECT  ks.kqbh, ks.kcdm, ks.tjdx FROM hisi_kshz ks  left join hisi_ks_zyclhz zy\n" +
            "on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh and ks.kcdm = zy.kcdm and ks.tjdx = zy.tjdx\n" +
            "WHERE ks.gm = '中型' and ks.sbbsm <>3 and zy.zycllx in ('TM', 'KZ', 'TD')  ${sql}  GROUP BY  ks.kqbh, ks.kcdm, ks.tjdx ) AS MIN \n" +
            "\t),\n" +
            "\tsmallcount AS (\n" +
            "\tSELECT COUNT\n" +
            "\t\t( * ) AS smallkcd \n" +
            "\tFROM\n" +
            "\t\t( SELECT  ks.kqbh, ks.kcdm, ks.tjdx FROM hisi_kshz ks  left join hisi_ks_zyclhz zy\n" +
            "on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh and ks.kcdm = zy.kcdm and ks.tjdx = zy.tjdx\n" +
            "WHERE ks.gm = '小型' and ks.sbbsm <>3 and zy.zycllx in ('TM', 'KZ', 'TD') ${sql} GROUP BY  ks.kqbh, ks.kcdm, ks.tjdx ) AS small \n" +
            "\t) SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tbigcount,\n" +
            "\tmediumcount,\n" +
            "\tsmallcount")
    Kcdgmtj selectKcdgmtjNew(String sql);

}


